using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using XProject.Entity;

namespace XProject.DAO
{
    class ContactDAO:DBAccess
    {
        #region sql command
        string SQL_SELECT_ID = "select max(ContactID) from Contact";
       //string SQL_SELECT_ALL = "select * from Contact";
        string SQL_SELECT_BY_PERSONID = "select * from Contact where PersonId=@personID";
        string SQL_INSERT = "insert into Contact(Address,PersonID) values(@address,@personID)";
        string SQL_UPDATE = "update Contact set Address=@address where ContactID=@contactID";
        string SQL_DELETE = "delete from Contact where ContactID=@contactID";
        #endregion
        #region fields
        string message = "";
        PhoneDAO daoPhone = new PhoneDAO();
        EmailDAO daoEmail = new EmailDAO();
        #endregion
        #region constructors
        #endregion
        #region properties
        public string Message
        {
            get { return message; }
        }
        #endregion
        #region methods
        public Contact SelectByPersonId(int personID)
        {
            Contact contact = null;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_PERSONID, conn);
                command.Parameters.Add("@personID", OleDbType.Integer).Value = personID;
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    
                    if (dr.Read())
                    {
                        Contact tmpcontact = new Contact();
                        tmpcontact.ContactID = dr.GetInt32(0);
                        tmpcontact.Address = dr.GetString(1);
                        tmpcontact.Phones = daoPhone.SelectByContact(tmpcontact.ContactID);
                        tmpcontact.Emails = daoEmail.SelectByContact(tmpcontact.ContactID);
                        contact = tmpcontact;
                    }
                }
   
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return contact;
        }
        /// <summary>
        /// Method to get a contact details by specified ID
        /// </summary>
        /// <param name="contactID"></param>
        /// <returns></returns>
        public Contact SelectById(int contactID)
        {
           Contact contact = null;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_SELECT_BY_PERSONID, conn);
                command.Parameters.AddWithValue("@contactId",contactID);
                if (Connect())
                {
                    OleDbDataReader dr = command.ExecuteReader();
                    if (dr.Read())
                    {
                        Contact tmpcontact = new Contact();
                        tmpcontact.ContactID = dr.GetInt32(0);
                        tmpcontact.Address = dr.GetString(2);
                        tmpcontact.Phones = daoPhone.SelectByContact(contact.ContactID);
                        tmpcontact.Emails = daoEmail.SelectByContact(contact.ContactID);
                        contact = tmpcontact;
                    }
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return contact;
        }
        /// <summary>
        /// Method to insert a new contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public int Insert(Contact contact,Person person)        
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_INSERT, conn);
                command.Parameters.AddWithValue("@address", OleDbType.VarChar).Value = contact.Address;
                command.Parameters.AddWithValue("@personID", OleDbType.Integer).Value = person.PersonID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    command.CommandText = SQL_SELECT_ID;
                    contact.ContactID = int.Parse(command.ExecuteScalar().ToString());
                    if (effectedRows > 0)
                    {
                        foreach (Phone phone in contact.Phones)
                        {
                            daoPhone.Insert(phone,contact.ContactID);
                        }
                        foreach (Email email in contact.Emails)
                        {
                            daoEmail.Insert(email,contact);
                        }
                    }                    
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        /// <summary>
        /// Method to update a contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public int Update(Contact contact)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_UPDATE, conn);
                command.Parameters.AddWithValue("@address", OleDbType.VarChar).Value=contact.Address;
                command.Parameters.AddWithValue("@contactID",OleDbType.Integer).Value=contact.ContactID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery(); 
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        /// <summary>
        /// Method to delete a contact
        /// </summary>
        /// <param name="contact"></param>
        /// <returns></returns>
        public int Delete(Contact contact)
        {
            int effectedRows = 0;
            try
            {
                OleDbCommand command = new OleDbCommand(SQL_DELETE, conn);
                command.Parameters.AddWithValue("@contactID", OleDbType.Integer).Value = contact.ContactID;
                if (Connect())
                {
                    effectedRows = command.ExecuteNonQuery();
                    Disconnect();
                }
            }
            catch (OleDbException ex)
            {
                message = ex.Message;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }
            return effectedRows;
        }
        #endregion
    }
}
